package com.anjian;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import javax.naming.NamingException;

import com.anjian.model.User;
import com.taobao.api.domain.Item;
import com.taobao.api.response.ItemUpdateResponse;

public class Dao {
	
	static Logger _logger = Logger.getLogger(Dao.class.getName());
	
	public static final Dao INSTANCE = new Dao();
	
	private Dao()
	{
		
	}
	
	public int clearErrorItems(String nick) throws Exception
	{
		Connection conn = null;
		PreparedStatement statement = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "delete from error_item_t where nick_c=?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, nick);
			return statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(conn, statement, null);
		}
	}
	
	public void addErrorItem(ItemUpdateResponse error) throws Exception
	{
		Connection conn = null;
		PreparedStatement statement = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "insert into error_item_t values(?,?,?,?,?)";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, error.getItem().getNumIid());
			statement.setString(2, error.getItem().getNick());
			statement.setString(3, error.getItem().getTitle());
			statement.setString(4, error.getSubCode());
			statement.setString(5, error.getSubMsg());
			statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(conn, statement, null);
		}
	}
	
	public List<ItemUpdateResponse> getErrorItems(String nick) throws Exception
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select * from error_item_t where nick_c=?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, nick);
			rs = statement.executeQuery();
			List<ItemUpdateResponse> items = new ArrayList<ItemUpdateResponse>();
			while (rs.next())
			{
				Item item = new Item();
				item.setNumIid(rs.getLong("num_iid_c"));
				item.setTitle(rs.getString("title_c"));
				ItemUpdateResponse response = new ItemUpdateResponse();
				response.setItem(item);
				items.add(response);
			}
			return items;
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
	}
	
	public void updateUser(long uid, String nick, String userAgent, String session) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "update user_t set session_c=?, last_login_c=?, user_agent_c=? where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, session);
			statement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			statement.setString(3, userAgent);
			statement.setLong(4, uid);
			int result = statement.executeUpdate();
			if (result == 0)
			{
				sql = "insert into user_t(user_id_c, nick_c, session_c, last_login_c, user_agent_c=?) values(?,?,?,?,?)";
				statement = conn.prepareStatement(sql);
				statement.setLong(1, uid);
				statement.setString(2, nick);
				statement.setString(3, session);
				statement.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
				statement.setString(5, userAgent);
			}
			statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(conn, statement, null);
		}
	}
	
	public List<User> getUsers() throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select * from user_t order by last_login_c limit 50 offset 0";
			statement = conn.prepareStatement(sql);
			rs = statement.executeQuery();
			List<User> users = new ArrayList<User>();
			while (rs.next())
			{
				User user = new User();
				user.setNick(rs.getString("nick_c"));
				user.setLastLogin(rs.getDate("last_login_c"));
				user.setUserAgent(rs.getString("user_agent_c"));
				users.add(user);
			}
			return users;
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
	}
}
